﻿
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System.Data;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Collections;
using NPOI.XSSF.UserModel;

namespace OutPutPermissions_App
{
    public class NPOIHelper
    {
        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public static bool ToExcel(DataTable table, string title, string filePath)
        {
            FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            IWorkbook workBook = new HSSFWorkbook();
            string sheetName = string.IsNullOrWhiteSpace(table.TableName) ? "sheet1" : table.TableName;
            ISheet sheet = workBook.CreateSheet(sheetName);

            //处理表格标题
            IRow row = sheet.CreateRow(0);
            row.CreateCell(0).SetCellValue(title);
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, table.Columns.Count - 1));
            row.Height = 500;

            ICellStyle cellStyle = workBook.CreateCellStyle();
            IFont font = workBook.CreateFont();
            font.FontName = "微软雅黑";
            font.FontHeightInPoints = 17;
            cellStyle.SetFont(font);
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.Alignment = HorizontalAlignment.Center;
            row.Cells[0].CellStyle = cellStyle;

            //处理表格列头
            row = sheet.CreateRow(1);
            for (int i = 0; i < table.Columns.Count; i++)
            {
                row.CreateCell(i).SetCellValue(table.Columns[i].ColumnName);
                row.Height = 350;
                sheet.AutoSizeColumn(i);
            }

            //处理数据内容
            for (int i = 0; i < table.Rows.Count; i++)
            {
                row = sheet.CreateRow(2 + i);
                row.Height = 250;
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    row.CreateCell(j).SetCellValue(table.Rows[i][j].ToString());
                    sheet.SetColumnWidth(j, 256 * 15);
                }
            }

            //写入数据流
            workBook.Write(fs);
            fs.Flush();
            fs.Close();

            return true;
        }

        public static bool ToExcel(DataTable table, string filePath)
        {
            FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            IWorkbook workBook = new HSSFWorkbook();
            //现在使用的仍然是生成Excel2003的Excel文件，由于03对行数（65535）和列数（255）有限制，所以当数据超出范围后难免出错
            //ArgumentException: Invalid column index (256). Allowable column range for BIFF8 is (0..255) or ('A'..'IV') ...
            if (Path.GetExtension(filePath).Equals(".xlsx", System.StringComparison.OrdinalIgnoreCase))
            {
                workBook = new XSSFWorkbook();
            }
            string sheetName = string.IsNullOrWhiteSpace(table.TableName) ? "sheet1" : table.TableName;
            ISheet sheet = workBook.CreateSheet(sheetName);


            ICellStyle cellStyle = workBook.CreateCellStyle();
            IFont font = workBook.CreateFont();
            font.FontName = "微软雅黑";
            font.FontHeightInPoints = 12;
            cellStyle.SetFont(font);
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.Alignment = HorizontalAlignment.Center;
            //row.Cells[0].CellStyle = cellStyle;


            //处理表格列头
            IRow row = sheet.CreateRow(0);
            for (int i = 0; i < table.Columns.Count; i++)
            {
                ICell cell = row.CreateCell(i);
                cell.SetCellValue(table.Columns[i].ColumnName);
                cell.CellStyle = cellStyle;
                row.Height = 500;
                sheet.AutoSizeColumn(i);
            }

            //处理数据内容
            for (int i = 0; i < table.Rows.Count; i++)
            {
                row = sheet.CreateRow(1 + i);
                row.Height = 250;
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    row.CreateCell(j).SetCellValue(table.Rows[i][j].ToString());
                    sheet.SetColumnWidth(j, 256 * 15);
                }
            }

            //写入数据流
            workBook.Write(fs);

            fs.Close();

            return true;
        }

        public static bool ToExcel(DataSet ds, string filePath)
        {
            FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            IWorkbook workBook = new HSSFWorkbook();
            //现在使用的仍然是生成Excel2003的Excel文件，由于03对行数（65535）和列数（255）有限制，所以当数据超出范围后难免出错
            //ArgumentException: Invalid column index (256). Allowable column range for BIFF8 is (0..255) or ('A'..'IV') ...
            if (Path.GetExtension(filePath).Equals(".xlsx", System.StringComparison.OrdinalIgnoreCase))
            {
                workBook = new XSSFWorkbook();
            }

            ICellStyle hearderStyle = GetCenter(workBook, 12);
            ICellStyle cellStyle = GetCenter(workBook, 8);


            foreach (DataTable table in ds.Tables)
            {
                string sheetName = string.IsNullOrWhiteSpace(table.TableName) ? "sheet1" : table.TableName;
                ISheet sheet = workBook.CreateSheet(sheetName);

                //处理表格列头
                IRow row = sheet.CreateRow(0);
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    ICell cell = row.CreateCell(i);
                    cell.SetCellValue(table.Columns[i].ColumnName);
                    cell.CellStyle = hearderStyle;
                    row.Height = 500;
                    sheet.AutoSizeColumn(i);
                }

                //处理数据内容
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    row = sheet.CreateRow(1 + i);
                    row.Height = 250;
                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        ICell cell=   row.CreateCell(j);
                        cell.SetCellValue(table.Rows[i][j].ToString());
                        cell.CellStyle = cellStyle;
                        sheet.SetColumnWidth(j, 256 * 15);
                    }
                }

                //固定列、行 滚动时不变
                sheet.CreateFreezePane(2, 1, 2, 1);
            }

            //写入数据流
            workBook.Write(fs);

            fs.Close();

            return true;
        }


        public static bool ToExcelsTree(DataTable table, string path, string title = null)
        {
            FileStream fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            IWorkbook workBook = new HSSFWorkbook();
            string sheetName = string.IsNullOrWhiteSpace(table.TableName) ? "Sheet1" : table.TableName;
            ISheet sheet = workBook.CreateSheet(sheetName);
            IRow row = null;
            int colNum = table.Columns.Count;

            int beginNum = 1;//排除列头，从1开始

            //处理表格标题
            if (!string.IsNullOrWhiteSpace(title))
            {
                row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue(title);
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, table.Columns.Count - 1));
                row.Height = 500;

                ICellStyle cellStyle = workBook.CreateCellStyle();
                IFont font = workBook.CreateFont();
                font.FontName = "微软雅黑";
                font.FontHeightInPoints = 17;
                cellStyle.SetFont(font);
                cellStyle.VerticalAlignment = VerticalAlignment.Center;
                cellStyle.Alignment = HorizontalAlignment.Center;
                row.Cells[0].CellStyle = cellStyle;

                //如果有标题，则累加1
                beginNum++;
            }


            //处理表格列头
            row = sheet.CreateRow(beginNum - 1);
            for (int i = 0; i < table.Columns.Count; i++)
            {
                string strVal = table.Columns[i].ColumnName;
                row.CreateCell(i).SetCellValue(strVal);
                row.Height = 350;
                sheet.AutoSizeColumn(i);
            }

            //处理数据内容
            for (int i = 0; i < table.Rows.Count; i++)
            {
                row = sheet.CreateRow(beginNum + i);
                row.Height = 250;
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    string strVal = table.Rows[i][j].ToString();
                    row.CreateCell(j).SetCellValue(strVal);
                    sheet.SetColumnWidth(j, 256 * 15);
                }
            }


            for (int i = 0; i < colNum; i++)
            {
                List<int> lstColWidth = new List<int>();
                string currVal = string.Empty;
                string nextVal = string.Empty;
                for (int j = beginNum; j <= sheet.LastRowNum; j++)
                {
                    currVal = sheet.GetRow(j).Cells[i].StringCellValue;

                    int mk = j;
                    if (!string.IsNullOrWhiteSpace(currVal))//排除 空值，空值不做合并处理
                    {
                        for (int k = j + 1; k <= sheet.LastRowNum; k++)
                        {
                            nextVal = sheet.GetRow(k).Cells[i].StringCellValue;

                            if (currVal != nextVal)
                            {
                                //因为k 累加所以导致当前值与下个值 不相同，所以记录 当前行数要 减去1
                                mk = k - 1;
                                break;
                            }
                            else if (k == sheet.LastRowNum) //边界值，处理最后一行，则提前Break 并记录当前 k
                            {
                                mk = k;
                                break;
                            }
                        }
                    }

                    if (mk != j)//排除 空值外，下个值的行数不等于当前行数，则需要合并
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(j, mk, i, i));
                    }

                    //if (i == 0) //如果是第一列，则 垂直水平居中
                    {
                        ICellStyle MergeStyle = workBook.CreateCellStyle();
                        MergeStyle.VerticalAlignment = VerticalAlignment.Center;
                        MergeStyle.Alignment = HorizontalAlignment.Center;
                        sheet.GetRow(j).Cells[i].CellStyle = MergeStyle;
                    }
                    //跳到执行下一个不同数据的行
                    j = mk;

                    //记录列长度
                    lstColWidth.Add(DataLength(currVal));
                }

                //设置列宽
                int maxWidth = lstColWidth.Max() * 200;
                sheet.SetColumnWidth(i, maxWidth);
            }

            //写入数据流
            workBook.Write(fs);
            fs.Flush();
            fs.Close();

            return true;
        }




        public static bool ToExcelTree(Hashtable htData, string path)
        {
            foreach (DictionaryEntry item in htData)
            {
                string tableName = item.Key.ToString();
                DataTable data = item.Value as DataTable;

                data.TableName = tableName;
                DataTree(path, data, 2);
            }

            return true;
        }

        public static void DataTree(string path, DataTable table, int treeIndex = 10000)
        {
            using (FileStream fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                IWorkbook workBook = new HSSFWorkbook();
                //现在使用的仍然是生成Excel2003的Excel文件，由于03对行数（65535）和列数（255）有限制，所以当数据超出范围后难免出错
                //ArgumentException: Invalid column index (256). Allowable column range for BIFF8 is (0..255) or ('A'..'IV') ...
                if (Path.GetExtension(path).Equals(".xlsx", System.StringComparison.OrdinalIgnoreCase))
                {
                    workBook = new XSSFWorkbook();
                }

                string sheetName = string.IsNullOrWhiteSpace(table.TableName) ? "Sheet1" : table.TableName;
                ISheet sheet = workBook.CreateSheet(sheetName);
                IRow row = null;
                int colNum = table.Columns.Count;
                if (treeIndex < table.Columns.Count || treeIndex > 0)
                {
                    colNum = treeIndex;
                }

                ICellStyle cellCenterStyle = GetCenter(workBook);

                int beginNum = 1;//排除列头，从1开始

                //处理表格列头
                row = sheet.CreateRow(beginNum - 1);
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    string strVal = table.Columns[i].ColumnName;
                    ICell cell = row.CreateCell(i);
                    cell.SetCellValue(strVal);
                    cell.CellStyle = cellCenterStyle;
                    row.Height = 350;
                    sheet.AutoSizeColumn(i);
                }

                //处理数据内容
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    row = sheet.CreateRow(beginNum + i);
                    row.Height = 250;
                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        string strVal = table.Rows[i][j].ToString();
                        ICell currCell = row.CreateCell(j);
                        currCell.SetCellValue(strVal);
                        currCell.CellStyle = cellCenterStyle;
                        sheet.SetColumnWidth(j, 256 * 15);
                    }
                }

                for (int i = 0; i < colNum; i++)
                {
                    List<int> lstColWidth = new List<int>();
                    string currVal = string.Empty;
                    string nextVal = string.Empty;
                    for (int j = beginNum; j <= sheet.LastRowNum; j++)
                    {
                        currVal = sheet.GetRow(j).Cells[i].StringCellValue;

                        int mk = j;
                        if (!string.IsNullOrWhiteSpace(currVal))//排除 空值，空值不做合并处理
                        {
                            for (int k = j + 1; k <= sheet.LastRowNum; k++)
                            {
                                nextVal = sheet.GetRow(k).Cells[i].StringCellValue;

                                if (currVal != nextVal)
                                {
                                    //因为k 累加所以导致当前值与下个值 不相同，所以记录 当前行数要 减去1
                                    mk = k - 1;
                                    break;
                                }
                                else if (k == sheet.LastRowNum) //边界值，处理最后一行，则提前Break 并记录当前 k
                                {
                                    mk = k;
                                    break;
                                }
                            }
                        }

                        if (mk != j)//排除 空值外，下个值的行数不等于当前行数，则需要合并
                        {
                            sheet.AddMergedRegion(new CellRangeAddress(j, mk, i, i));
                        }

                        //if (i == 0) //如果是第一列，则 垂直水平居中
                        {
                            sheet.GetRow(j).Cells[i].CellStyle = cellCenterStyle;
                        }
                        //跳到执行下一个不同数据的行
                        j = mk;

                        //记录列长度
                        lstColWidth.Add(DataLength(currVal));
                    }

                    //设置列宽
                    int maxWidth = lstColWidth.Max() * 200;
                    sheet.SetColumnWidth(i, maxWidth);
                }

                //隐藏ID列
                sheet.SetColumnHidden(3, true);

                //固定列、行 滚动时不变
                sheet.CreateFreezePane(3, 1, 3, 1);

                //写入数据流
                workBook.Write(fs);
            }
        }


        private static ICellStyle GetCenter(IWorkbook workBook, short fontSize = 10, HorizontalAlignment align = HorizontalAlignment.Center)
        {
            ICellStyle cellStyle = workBook.CreateCellStyle();
            IFont font = workBook.CreateFont();
            font.FontName = "微软雅黑";
            font.FontHeightInPoints = fontSize;
            cellStyle.SetFont(font);
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.Alignment = align;
            return cellStyle;
        }


        /// <summary>
        /// 获取字符串长度（中文按2个字节长度）
        /// </summary>
        /// <param name="stringWithEnglishAndChinese"></param>
        /// <returns></returns>
        private static int DataLength(string stringWithEnglishAndChinese)
        {
            int lng = 0;
            for (int i = 0; i < stringWithEnglishAndChinese.Length; i++)
            {
                byte[] b = System.Text.Encoding.Default.GetBytes(stringWithEnglishAndChinese.Substring(i, 1));
                if (b.Length > 1)
                    lng += 2;
                else
                    lng += 1;
            }
            return lng;
        }


    }
}
